﻿/***************************************************************
 * 开发人员：网魂小兵@http://www.mozlite.com
 * 
 * 数据库提供者基类，所有数据库提供者都继承此类。
 * 
 * 最后修改：2011-10-25
 * ************************************************************/
namespace Mozlite.Data
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using Mozlite.Configuration;
    using System.Data.Common;
    using System.Collections;
    using System.Text.RegularExpressions;

    /// <summary>
    /// 数据库处理方法扩展类。
    /// </summary>
    public static class DataExtension { 
        /// <summary>
        /// 数据库事务回滚，并且关闭数据库连接。
        /// </summary>
        /// <param name="transaction">当前数据库事务实例对象。</param>
        public static void RollbackTransaction(this DbTransaction transaction)
        {
            try
            {
                transaction.Rollback();
            }
            finally
            {
                if (transaction != null && transaction.Connection != null)
                {
                    transaction.Connection.Close();
                }
            }
        }
        /// <summary>
        /// 提交数据库事务，并且关闭现有连接。
        /// </summary>
        /// <param name="transaction">当前数据库事务实例对象。</param>
        public static void CommitTransaction(this DbTransaction transaction)
        {
            try
            {
                transaction.Commit();
            }
            finally
            {
                if (transaction != null && transaction.Connection != null)
                {
                    transaction.Connection.Close();
                }
            }
        }
    }

    /// <summary>
    /// 数据提供者基类。
    /// </summary>
    /// <typeparam name="T">当前提供者抽象类。</typeparam>
    public abstract class ProviderBase<T> where T : ProviderBase<T>
    {
        #region databases
        private DbProviderFactory factory;
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
        /// <summary>
        /// 获取数据库链接实例对象。
        /// </summary>
        /// <returns>返回数据库链接实例对象。</returns>
        public DbConnection GetConnection()
        {
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = Settings.ConnectionString;
            return connection;
        }
        /// <summary>
        /// 获取当前开始事务的实例对象。
        /// </summary>
        /// <returns>返回当前开始事务的实例对象。</returns>
        public DbTransaction GetTransaction() {
            var connection = GetConnection();
            connection.Open();
            return connection.BeginTransaction();
        }
        /// <summary>
        /// 配置属性。
        /// </summary>
        public Provider Settings { get; private set; }

        /// <summary>
        /// 执行查询字符串，并返回是否执行成功。
        /// </summary>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="args">格式化参数。</param>
        /// <returns>返回是否执行成功。</returns>
        public bool ExecuteNonQuery(string cmdText, params object[] args)
        {
            return this.ExecuteNonQuery(string.Format(cmdText, args));
        }
        /// <summary>
        /// 执行查询字符串，并返回是否执行成功。
        /// </summary>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回是否执行成功。</returns>
        public bool ExecuteNonQuery(string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text)
        {
            DbCommand cmd = factory.CreateCommand();

            using (DbConnection conn = GetConnection())
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int rowAffected = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
                return rowAffected > 0;
            }
        }
        /// <summary>
        /// 执行查询字符串，并返回是否执行成功。
        /// </summary>
        /// <param name="connection">数据库链接实例对象。</param>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回是否执行成功。</returns>
        public bool ExecuteNonQuery(DbConnection connection, string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text)
        {
            DbCommand cmd = factory.CreateCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int rowAffected = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return rowAffected > 0;
        }
        /// <summary>
        /// 执行查询字符串，并返回是否执行成功。
        /// </summary>
        /// <param name="trans">事务实例。</param>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回是否执行成功。</returns>
        public bool ExecuteNonQuery(DbTransaction trans, string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text)
        {
            DbCommand cmd = factory.CreateCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int rowAffected = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return rowAffected > 0;
        }
        /// <summary>
        /// 执行查询字符串，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="args">格式化参数。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public IDataReader ExecuteReader(string cmdText, params object[] args)
        {
            return this.ExecuteReader(string.Format(cmdText, args));
        }
        /// <summary>
        /// 执行查询字符串，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public IDataReader ExecuteReader(string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text)
        {
            DbCommand cmd = factory.CreateCommand();
            DbConnection conn = GetConnection();
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }
        /// <summary>
        /// 执行查询字符串，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="connection">数据库链接实例对象。</param>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public IDataReader ExecuteReader(DbConnection connection, string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text)
        {
            DbCommand cmd = factory.CreateCommand();
            try
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// 执行查询字符串，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="trans">事务实例。</param>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public IDataReader ExecuteReader(DbTransaction trans, string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text)
        {
            DbCommand cmd = factory.CreateCommand();
            try
            {
                PrepareCommand(cmd, trans.Connection, null, cmdType, cmdText, commandParameters);
                IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// 执行查询字符串，并返回实例对象。
        /// </summary>
        /// <typeparam name="V">读取实例，此类型构造函数需要为<see cref="T:System.Data.IDataReader"/>实例；或者为<see cref="T:Mozlite.BaseObject"/>的子类。</typeparam>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="args">格式化参数。</param>
        /// <returns>返回实例对象。</returns>
        public V ExecuteReader<V>(string cmdText, params object[] args) where V : class,new()
        {
            return this.ExecuteReader(cmdText, args).TGet<V>();
        }
        /// <summary>
        /// 执行查询字符串，并返回实例对象。
        /// </summary>
        /// <typeparam name="V">读取实例，此类型构造函数需要为<see cref="T:System.Data.IDataReader"/>实例；或者为<see cref="T:Mozlite.BaseObject"/>的子类。</typeparam>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public V ExecuteReader<V>(string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text) where V : class,new()
        {
            return ExecuteReader(cmdText, commandParameters, cmdType).TGet<V>();
        }
        /// <summary>
        /// 执行查询字符串，并返回实例对象。
        /// </summary>
        /// <typeparam name="V">读取实例，此类型构造函数需要为<see cref="T:System.Data.IDataReader"/>实例；或者为<see cref="T:Mozlite.BaseObject"/>的子类。</typeparam>
        /// <param name="connection">数据库链接实例对象。</param>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public V ExecuteReader<V>(DbConnection connection, string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text) where V : class,new()
        {
            return ExecuteReader(connection, cmdText, commandParameters, cmdType).TGet<V>();
        }
        /// <summary>
        /// 执行查询字符串，并返回实例对象。
        /// </summary>
        /// <typeparam name="V">读取实例，此类型构造函数需要为<see cref="T:System.Data.IDataReader"/>实例；或者为<see cref="T:Mozlite.BaseObject"/>的子类。</typeparam>
        /// <param name="trans">事务实例。</param>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public V ExecuteReader<V>(DbTransaction trans, string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text) where V : class,new()
        {
            return ExecuteReader(trans, cmdText, commandParameters, cmdType).TGet<V>();
        }
        /// <summary>
        /// 执行查询字符串，并返回执行结果实例对象。
        /// </summary>
        /// <typeparam name="V">返回值的类型。</typeparam>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="args">格式化参数。</param>
        /// <returns>返回执行结果实例对象。</returns>
        public V ExecuteScalar<V>(string cmdText, params object[] args)
        {
            return this.ExecuteScalar<V>(string.Format(cmdText, args));
        }
        /// <summary>
        /// 执行查询字符串，并返回执行结果实例对象。
        /// </summary>
        /// <typeparam name="V">返回值的类型。</typeparam>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回执行结果实例对象。</returns>
        public V ExecuteScalar<V>(string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text)
        {
            DbCommand cmd = factory.CreateCommand();

            using (DbConnection connection = GetConnection())
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val.ChangeTo<V>();
            }
        }
        /// <summary>
        /// 执行查询字符串，并返回执行结果实例对象。
        /// </summary>
        /// <typeparam name="V">返回值的类型。</typeparam>
        /// <param name="connection">数据库链接实例对象。</param>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回执行结果实例对象。</returns>
        public V ExecuteScalar<V>(DbConnection connection, string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text)
        {
            DbCommand cmd = factory.CreateCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val.ChangeTo<V>();
        }
        /// <summary>
        /// 执行查询字符串，并返回执行结果实例对象。
        /// </summary>
        /// <typeparam name="V">返回值的类型。</typeparam>
        /// <param name="trans">事务实例。</param>
        /// <param name="cmdText">SQL查询字符串。</param>
        /// <param name="commandParameters">参数实例。</param>
        /// <param name="cmdType">命令类型。</param>
        /// <returns>返回执行结果实例对象。</returns>
        public V ExecuteScalar<V>(DbTransaction trans, string cmdText, DbParameter[] commandParameters = null, CommandType cmdType = CommandType.Text)
        {
            DbCommand cmd = factory.CreateCommand();

            PrepareCommand(cmd, trans.Connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val.ChangeTo<V>();
        }
        /// <summary>
        /// 缓存参数。
        /// </summary>
        /// <param name="cacheKey">缓存键。</param>
        /// <param name="commandParameters">参数实例对象。</param>
        public void CacheParameters(string cacheKey, params DbParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }
        /// <summary>
        /// 获取缓存的参数数组。
        /// </summary>
        /// <param name="cacheKey">缓存键。</param>
        /// <returns>返回缓存的参数数组。</returns>
        public DbParameter[] GetCachedParameters(string cacheKey)
        {
            DbParameter[] cachedParms = (DbParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            DbParameter[] clonedParms = new DbParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (DbParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        private void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion

        #region helpers
        /// <summary>
        /// 最小SQL时间，1753-1-1。
        /// </summary>
        public static readonly DateTime MinSqlDateTime = new DateTime(1753, 1, 1);
        /// <summary>
        /// 最大SQL时间，9999-12-31。
        /// </summary>
        public static readonly DateTime MaxSqlDateTime = new DateTime(9999, 12, 13);
        /// <summary>
        /// 格式化对象，并返回SQL安全字符串。
        /// </summary>
        /// <param name="value">需要插入SQL语句的对象。</param>
        /// <returns>如果为枚举则返回数字字符串，如果为布尔型，则True返回1，false返回0字符串。</returns>
        public static string Q(object value)
        {
            if (value == null || string.IsNullOrEmpty(value.ToString()))
                return null;
            Type type = value.GetType();
            if (type.IsEnum)
            {
                return ((int)value).ToString();
            }
            if (type == typeof(bool))
                return ((bool)value) ? "1" : "0";
            return value.SQLEncode();
        }
        /// <summary>
        /// 格式化对象，用于参数设置。
        /// </summary>
        /// <param name="value">传给参数的对象。</param>
        /// <returns>如果value为null，则返回DBNull.Value，否则返回本身。</returns>
        public static object N(object value)
        {
            if (value == null)
                return DBNull.Value;
            return value;
        }
        /// <summary>
        /// 格式化对象，用于参数设置。
        /// </summary>
        /// <param name="value">传给参数的GUID对象。</param>
        /// <returns>如果value为Guid.Empty，则返回DBNull.Value，否则返回本身。</returns>
        public static object N(Guid value)
        {
            if (value == Guid.Empty)
                return DBNull.Value;
            return value;
        }
        /// <summary>
        /// 格式化日期对象(1753-1-1到9999-12-31)，用于SqlServer参数设置。
        /// </summary>
        /// <param name="datetime">当前对象。</param>
        /// <returns>返回安全区域的时间。</returns>
        public static DateTime N(DateTime datetime)
        {
            if (datetime <= MinSqlDateTime)
                return MinSqlDateTime;
            if (datetime >= MaxSqlDateTime)
                return MaxSqlDateTime;
            return datetime;
        }
        
        private static readonly Regex dbOwnerRegex = new Regex("(\\{dbowner\\})", RegexOptions.IgnoreCase|RegexOptions.Compiled|RegexOptions.Multiline);
        private static readonly Regex dbProfixRegex = new Regex("(\\{dbprofix\\})",RegexOptions.IgnoreCase|RegexOptions.Compiled|RegexOptions.Multiline);
        /// <summary>
        /// 格式化脚本，前后缀替换。
        /// </summary>
        /// <param name="sql">数据库脚本字符串。</param>
        /// <returns>返回格式化后的字符串。</returns>
        public string EscapeScript(string sql){
            return dbOwnerRegex.Replace(dbProfixRegex.Replace(sql, Settings.DbProfix), Settings.DbOwner);
        }
        /// <summary>
        /// 添加表格或存储过程前面的数据库所有者和表格前缀。
        /// </summary>
        /// <param name="procedureNameOrDbName">存储过程或表格名称。</param>
        /// <returns>返回添加后的字符串。</returns>
        public string AddProfix(string procedureNameOrDbName) {
            return Settings.DbOwner + Settings.DbProfix + procedureNameOrDbName;
        }
        #endregion

        #region initializers
        /// <summary>
        /// 初始化数据库提供者基类。
        /// </summary>
        /// <param name="factory">数据库提供者工厂实例对象。</param>
        protected ProviderBase(DbProviderFactory factory)
        {
            this.factory = factory;
        }

        private static readonly Dictionary<string, T> providers = new Dictionary<string, T>(StringComparer.OrdinalIgnoreCase);
        private static readonly object locker = new object();
        
        /// <summary>
        /// 创建实例对象。
        /// </summary>
        /// <param name="providerName">数据提供者名称。</param>
        /// <returns>返回当前实例对象。</returns>
        public static T CreateInstance(string providerName)
        {
            Provider provider = DataProviders.GetProviders().Current[providerName];
            if (provider != null)
            {
                Type type = Type.GetType(provider.Type);
                T instance = default(T);
                if (type != null)
                {
                    instance = (T)Activator.CreateInstance(type);
                }
                if (instance == null)
                {
                    throw new Exception(provider.Name + " is error!");
                }
                instance.Settings = provider;
                return instance;
            }
            throw new Exception(providerName + " is error!");
        }

        /// <summary>
        /// 获取当前对象实例。
        /// </summary>
        public static T Current
        {
            get
            {
                string name = typeof(T).Name;
                T provider = default(T);
                lock (locker)
                {
                    if (!providers.TryGetValue(name, out provider))
                    {
                        provider = CreateInstance(name);
                        if (provider != null)
                            providers.Add(name, provider);
                    }
                }
                return provider;
            }
        }
        #endregion
    }
}